package com.mlink.vs.service;

import com.mlink.vs.entity.PiccUser;
import com.mlink.vs.entity.PiccUserOnlineStatus;
import com.mlink.vs.entity.UCResult;
import com.mlink.vs.sso.BaseAction;
import com.mlink.vs.util.Constants;

import java.io.IOException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import javax.annotation.Generated;

import org.json.JSONArray;
import org.json.JSONException;
import org.json.JSONObject;
import org.seasar.extension.jdbc.JdbcManager;

import static com.mlink.vs.entity.PiccUserNames.*;
import static org.seasar.extension.jdbc.operation.Operations.*;

/**
 * {@link PiccUser}のサービスクラスです。
 * 
 */
@Generated(value = { "S2JDBC-Gen 2.4.47",
		"org.seasar.extension.jdbc.gen.internal.model.ServiceModelFactoryImpl" }, date = "2017-9-7 10:59:16")
public class PiccUserService extends AbstractService<PiccUser> {

	public void setJdbcManager(JdbcManager jdbcManager) {
		this.jdbcManager = jdbcManager;
	}

	/**
	 * 識別子でエンティティを検索します。
	 * 
	 * @param userId
	 *            識別子
	 * @return エンティティ
	 */
	public PiccUser findById(String userId) {
		return select().id(userId).getSingleResult();
	}

	/**
	 * 識別子の昇順ですべてのエンティティを検索します。
	 * 
	 * @return エンティティのリスト
	 */
	public List<PiccUser> findAllOrderById() {
		return select().orderBy(asc(userId())).getResultList();
	}

	public PiccUser findUser(String uid, String passwd) {
		return jdbcManager.from(PiccUser.class).where("USER_ID = ? and PASSWORD= ? ", uid, passwd).getSingleResult();
	}

	public List<PiccUser> findLikeByIdAndName(String keywords) {

		String link = "%" + keywords + "%";
		return jdbcManager.from(PiccUser.class).where("USER_ID LIKE ? or USER_NAME LIKE ?", link, link).getResultList();

	}

	// 总条数
	public int findTotalCount() {
		List<Object> params = new ArrayList<Object>();
		String sql = "select count(1) from PICC_USER nc where   IS_ONLINE=?";

		params.add("1");
		return jdbcManager.selectBySql(int.class, sql, params.toArray()).getSingleResult();
	}

	// 本组总条数
	public int findTotalCountt() {
		List<Object> params = new ArrayList<Object>();
		String sql = "select count(1) from PICC_USER nc where   IS_ONLINE=?";

		params.add("1");
		return jdbcManager.selectBySql(int.class, sql, params.toArray()).getSingleResult();
	}

	// 获取在线坐席用户
	public List<PiccUser> findAllByOnline() {

		List<Object> params = new ArrayList<Object>();
		String sql = "select  * from PICC_USER where  IS_ONLINE = ? ";
		params.add("1");
		return jdbcManager.selectBySql(PiccUser.class, sql, params.toArray()).getResultList();

	}

	// 查找组下所有用户
	public List<PiccUser> findAllByGroup(PiccUser user) {

		List<Object> params = new ArrayList<Object>();
		String sql = "select  * from PICC_USER where  FIRST_SECTOR_NAME = ? and SECOND_SECTOR_NAME = ? and THIRD_SECTOR_NAME = ? and GROUP_NAME = ?   ";
		params.add(user.firstSectorName);
		params.add(user.secondSectorName);
		params.add(user.thirdSectorName);
		params.add(user.groupName);
		return jdbcManager.selectBySql(PiccUser.class, sql, params.toArray()).getResultList();

	}

	public List<Map> findSectorName() {

		String sql = " SELECT DEPT FROM " + " ((SELECT DISTINCT FIRST_SECTOR_NAME AS DEPT " + " FROM PICC_USER) "
				+ " UNION " + " (SELECT DISTINCT CONCAT(FIRST_SECTOR_NAME, '-', SECOND_SECTOR_NAME) AS DEPT "
				+ " FROM PICC_USER " + " WHERE FIRST_SECTOR_NAME IS NOT NULL AND SECOND_SECTOR_NAME IS NOT NULL) "
				+ " UNION "
				+ " (SELECT DISTINCT CONCAT(FIRST_SECTOR_NAME, '-', SECOND_SECTOR_NAME, '-', THIRD_SECTOR_NAME) AS DEPT "
				+ " FROM PICC_USER " + " WHERE FIRST_SECTOR_NAME IS NOT NULL  "
				+ " AND SECOND_SECTOR_NAME IS NOT NULL  " + " AND THIRD_SECTOR_NAME IS NOT NULL) " + " UNION "
				+ " (SELECT DISTINCT CONCAT(FIRST_SECTOR_NAME, '-', SECOND_SECTOR_NAME,  "
				+ " '-', THIRD_SECTOR_NAME, '-', FOURTH_SECTOR_NAME) AS DEPT " + " FROM PICC_USER "
				+ " WHERE FIRST_SECTOR_NAME IS NOT NULL  " + " AND SECOND_SECTOR_NAME IS NOT NULL  "
				+ " AND THIRD_SECTOR_NAME IS NOT NULL " + " AND FOURTH_SECTOR_NAME IS NOT NULL) " + " UNION "
				+ " (SELECT DISTINCT CONCAT(FIRST_SECTOR_NAME, '-', SECOND_SECTOR_NAME,  "
				+ " '-', THIRD_SECTOR_NAME, '-', FOURTH_SECTOR_NAME, '-', FIFTH_SECTOR_NAME) AS DEPT "
				+ " FROM PICC_USER " + " WHERE FIRST_SECTOR_NAME IS NOT NULL  "
				+ " AND SECOND_SECTOR_NAME IS NOT NULL  " + " AND THIRD_SECTOR_NAME IS NOT NULL "
				+ " AND FOURTH_SECTOR_NAME IS NOT NULL " + " AND FIFTH_SECTOR_NAME IS NOT NULL)) AS T "
				+ " ORDER BY DEPT ";

		List<Map> result = jdbcManager.selectBySql(Map.class, sql).getResultList();
		return result;
	}

	public List<PiccUser> findByGroupName(String groupName, String userType1, String userType2) {
		String sql = "select * from PICC_USER  where GROUP_NAME=? and (USER_TYPE=? or USER_TYPE=?)  ORDER BY USER_TYPE desc";
		return jdbcManager.selectBySql(PiccUser.class, sql, groupName, userType1, userType2).getResultList();
	}

	public List<PiccUser> findZuoXi(String groupName, String isOnline, String userType1, String userType2) {
		String sql = "select * from PICC_USER  where GROUP_NAME=? AND IS_ONLINE = ? and (USER_TYPE=? or USER_TYPE=?)";
		return jdbcManager.selectBySql(PiccUser.class, sql, groupName, isOnline, userType1, userType2).getResultList();
	}

	public List<PiccUser> findLeader(String groupName, String userType2) {
		String sql = "select * from PICC_USER  where GROUP_NAME=? AND USER_TYPE = ? ";
		return jdbcManager.selectBySql(PiccUser.class, sql, groupName, userType2).getResultList();
	}

	public int findIsOnlineCount(String firstSectorName, String secondSectorName, String thirdSectorName,
			String groupName, String isOnline, String userType1, String userType2) {
		List<Object> params = new ArrayList<Object>();
		String sql = "select count(1) from PICC_USER where FIRST_SECTOR_NAME=? and SECOND_SECTOR_NAME=? and THIRD_SECTOR_NAME=? "
				+ " and GROUP_NAME=? and IS_ONLINE=? and ( USER_TYPE=? or USER_TYPE=? )";
		params.add(firstSectorName);
		params.add(secondSectorName);
		params.add(thirdSectorName);
		params.add(groupName);
		params.add(isOnline);
		params.add(userType1);
		params.add(userType2);
		return jdbcManager.selectBySql(int.class, sql, params.toArray()).getSingleResult();
	}

	public List<PiccUser> findIsOnline(String firstSectorName, String secondSectorName, String thirdSectorName,
			String groupName, String isOnline, String userType1, String userType2) {
		List<Object> params = new ArrayList<Object>();
		String sql = "select * from PICC_USER where FIRST_SECTOR_NAME=? and SECOND_SECTOR_NAME=? and THIRD_SECTOR_NAME=? "
				+ " and GROUP_NAME=? and IS_ONLINE=? and ( USER_TYPE=? or USER_TYPE=? )";
		params.add(firstSectorName);
		params.add(secondSectorName);
		params.add(thirdSectorName);
		params.add(groupName);
		params.add(isOnline);
		params.add(userType1);
		params.add(userType2);
		return jdbcManager.selectBySql(PiccUser.class, sql, params.toArray()).getResultList();
	}

	public int updateStatus(String line, String userId) {
		String sql = "update PICC_USER set IS_ONLINE = ? where USER_ID = ? ";
		return jdbcManager.updateBySql(sql, String.class, String.class).params(line, userId).execute();
	}
	
	public List<PiccUser> findByGroupName(String groupName, String userType) {
		String sql = "select * from PICC_USER  where GROUP_NAME=? and USER_TYPE=? ";
		return jdbcManager.selectBySql(PiccUser.class , sql ,groupName,userType).getResultList();
	}

	public PiccUser getUserInfo(String userId, String token) {
		try {
			JSONObject body = new JSONObject();
			UCResult resuelt = null;
			body.put("TOKEN", token);
			body.put("userid", userId);

			resuelt = BaseAction.requeryUC("UAC_USER_INFO", body);
		
			if (resuelt.code.equals("1")) {
				PiccUser pu = new PiccUser();
				pu.userId = userId;
				pu.userName = resuelt.body.getJSONObject("user").getString("truename");
				pu.userType = resuelt.body.getJSONObject("user").getString("role");
				pu.userContact = resuelt.body.getJSONObject("user").getString("photo");
				pu.userCompany = resuelt.body.getJSONObject("user").getString("userCompany");
				pu.groupName = resuelt.body.getJSONObject("user").getString("groupID"); // groupname 对应 groupid
				pu.groupTrueName = resuelt.body.getJSONObject("user").getString("orgName");  //  groupTrueName 对应 groupname
			
				return pu;
			} else {

				return null;
			}
		} catch (JSONException e1) {
			// TODO Auto-generated catch block
			e1.printStackTrace();
		}
		return null;
	}

}